package com.study;

import com.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class Test01 {
    public static void main(String[] args) throws SQLException {
        insertDataHandle();
        //deleteDateHandle();
        //updateDateHandle();
        //selectBeanHandler();
        //selectBeanListHandler();
        //selectScalarHandler();
        //selectColumnListHandler();
        //selectMapListHandler();
    }
    public static void insertDataHandle(){
        // 1. 获取QueryRunner对象，用于执行sql语句      com.mysql.cj.jdbc.ConnectionImpl@27082746
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        // 2. 指定sql语句，传参，执行sql
        String sql = "insert into tb_product values(null, ?, ?, ?)";
        Object[] params = {"统一", 8, "c005"};
        try {
            int line = queryRunner.update(sql, params);
            // 3. 处理结果
            System.out.println("受影响的行数：" + line);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void deleteDateHandle() throws SQLException {
        //1.获取QueryRunner对象，用于执行sql语句
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //2.指定sql语句，传参，执行sql
        String sql = "delete from tb_product where pid = ?";
        int line = queryRunner.update(sql, 15);
        //3.处理结果
        System.out.println("受影响的行数是：" + line);
    }

    public static void updateDateHandle() throws SQLException {
        //1.获取QueryRunner对象，用于执行sql语句
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //2.书写sql语句，传参，执行sql语句
        String sql = "update tb_product set pname=? where pid=? ";
        Object[] params = {"统一方便面",15};
        int line = queryRunner.update(sql, params);
        //3.处理结果
        System.out.println("受影响的行数是：" + line);

    }

    public static void selectBeanHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select * from tb_product where pid = ?";
        Object[] params = {14};
        Product product = queryRunner.query(sql, new BeanHandler<Product>(Product.class), params);
        System.out.println(product);
    }

    public static void selectBeanListHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from tb_product where pname like ?";
        Object[] params = {"%想%"};

        List<Product> products = queryRunner.query(sql, new BeanListHandler<Product>(Product.class), params);
        for (Product product : products) {
            System.out.println("Product = " + product );
        }
    }

    public static void selectScalarHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select count(pid) from tb_product where category_id = ?";
        Object[] params = {"c001"};

        Long num = queryRunner.query(sql, new ScalarHandler<Long>(),params);
        System.out.println("统计的个数为：" + num);
    }

    public static void selectColumnListHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from tb_product";
        List<String> pnameList = queryRunner.query(sql, new ColumnListHandler<String>("pname"));
        for(String pname : pnameList){
            System.out.println("商品名称：" + pname);
        }
    }

    public static void selectMapListHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select pname, category_id from tb_product where category_id = ?";
        Object[] params = {"c001"};
        List<Map<String, Object>> mapList = queryRunner.query(sql, new MapListHandler(), params);
        for(Map<String, Object> map : mapList){
            System.out.println(map);
        }
    }




}
